{% extends "base.html" %}

{% block title %}Audit Database - Archive Audit System{% endblock %}

{% block content %}
<h2>Audit Database Archive</h2>

<div class="card mb-4">
    <div class="card-header">
        <h5>Archive Information</h5>
    </div>
    <div class="card-body">
        <dl class="row">
            <dt class="col-sm-3">Archive ID</dt>
            <dd class="col-sm-9">{{ archive.id }}</dd>
            
            <dt class="col-sm-3">Original Database</dt>
            <dd class="col-sm-9">{{ archive.original_db }}</dd>
            
            <dt class="col-sm-3">Archive Date</dt>
            <dd class="col-sm-9">{{ archive.archive_date }}</dd>
            
            <dt class="col-sm-3">Archived By</dt>
            <dd class="col-sm-9">{{ archive.archived_by }}</dd>
            
            <dt class="col-sm-3">Description</dt>
            <dd class="col-sm-9">{{ archive.description }}</dd>
        </dl>
    </div>
</div>

<div class="card mb-4">
    <div class="card-header">
        <h5>Execute SQL Query</h5>
    </div>
    <div class="card-body">
        <form method="post">
            <div class="mb-3">
                <label for="sql_query" class="form-label">SQL Query</label>
                <textarea class="form-control" id="sql_query" name="sql_query" rows="3" required>SELECT * FROM sqlite_master WHERE type='table';</textarea>
                <div class="form-text">Enter a SQL query to execute against this archived database.</div>
            </div>
            <button type="submit" class="btn btn-primary">Execute Query</button>
        </form>
    </div>
</div>

{% if error %}
<div class="alert alert-danger">
    <strong>Error:</strong> {{ error }}
</div>
{% endif %}

{% if results %}
<div class="card">
    <div class="card-header">
        <h5>Query Results</h5>
    </div>
    <div class="card-body">
        <div class="table-responsive">
            <table class="table table-striped table-hover">
                <thead>
                    <tr>
                        {% for column in columns %}
                        <th>{{ column }}</th>
                        {% endfor %}
                    </tr>
                </thead>
                <tbody>
                    {% for row in results %}
                    <tr>
                        {% for column in columns %}
                        <td>{{ row[column] }}</td>
                        {% endfor %}
                    </tr>
                    {% endfor %}
                </tbody>
            </table>
        </div>
        <div class="mt-3">
            <button class="btn btn-outline-secondary" id="exportCsv">Export to CSV</button>
        </div>
    </div>
</div>
{% endif %}
{% endblock %}

{% block extra_js %}
{% if results %}
<script>
document.addEventListener('DOMContentLoaded', function() {
    document.getElementById('exportCsv').addEventListener('click', function() {
        // Get the table data
        const table = document.querySelector('table');
        const rows = table.querySelectorAll('tr');
        let csv = [];
        
        // Get headers
        const headers = [];
        const headerCells = rows[0].querySelectorAll('th');
        headerCells.forEach(cell => {
            headers.push('"' + cell.textContent.trim() + '"');
        });
        csv.push(headers.join(','));
        
        // Get data rows
        for (let i = 1; i < rows.length; i++) {
            const row = rows[i];
            const cells = row.querySelectorAll('td');
            const rowData = [];
            cells.forEach(cell => {
                rowData.push('"' + cell.textContent.trim() + '"');
            });
            csv.push(rowData.join(','));
        }
        
        // Create CSV file
        const csvContent = csv.join('\n');
        const blob = new Blob([csvContent], { type: 'text/csv' });
        const url = URL.createObjectURL(blob);
        
        // Create download link
        const a = document.createElement('a');
        a.setAttribute('href', url);
        a.setAttribute('download', 'query_results.csv');
        a.style.display = 'none';
        document.body.appendChild(a);
        a.click();
        document.body.removeChild(a);
    });
});
</script>
{% endif %}
{% endblock %}
